"""
Case Type   : DBlink功能
Case Name   : DBlink dblink_build_sql_update功能验证
Create At   : 2024/4/28
Owner       : peilinqian
Description :
    PS：用例涉及前置操作及切库操作，结合py脚本实现自动化
    1、创建数据库及库下创建dblink扩展
    2、执行sql脚本
    3、比对执行结果（实际结果与预期脚本文件比对）
Expect      :
    1、创建数据库及库下创建dblink扩展，成功
    2、执行sql脚本，成功
    3、比对执行结果（实际结果与预期脚本文件比对），结果一致
History     :
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.com = Common()
        self.constant = Constant()
        self.db_name = 'db_dblink_case0008'
        self.sql_name = 'dblink_case0008.sql'
        self.expect_name = 'dblink_case0008.out'
        self.result_name = 'dblink_case0008_result.out'
        self.source_dir = os.path.join(macro.SCRIPTS_PATH, "dblink")
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "dblink")

        self.log.info('----远程复制sql及expec文件，并修改权限----')
        self.com.scp_file(self.pri_root,
                          f"{self.sql_name}", self.target_path,
                          source_dir=self.source_dir)
        self.com.scp_file(self.pri_root,
                          f"{self.expect_name}", self.target_path,
                          source_dir=self.source_dir)
        chmod_cmd = f"chmod 777 -R {self.target_path};" \
                    f"ls -al {self.target_path}"
        self.log.info(chmod_cmd)
        chmod_result = self.pri_root.sh(chmod_cmd).result()
        self.log.info(chmod_result)

    def test_main(self):
        step_txt = '----step1:创建数据库及库下创建dblink扩展; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name};" \
                 f"create database {self.db_name}"
        db_result = self.pri_sh.execut_db_sql(db_sql)
        create_sql = f"create extension dblink;"
        create_result = self.pri_sh.execut_db_sql(create_sql,
                                                  dbname=self.db_name)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_result,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.create_extension_success, create_result,
                      "执行失败" + step_txt)

        step_txt = '----step2: 执行sql脚本; expect:执行成功----'
        self.log.info(step_txt)
        shell = f'cd {self.target_path};' \
                f'source {macro.DB_ENV_PATH}; ' \
                f'gsql -d {self.db_name} ' \
                f'-p {self.pri_dbuser.db_port} ' \
                f'-a < {self.sql_name} > {self.result_name} 2>&1'
        self.log.info(shell)
        result = self.pri_dbuser.sh(shell).result()
        self.log.info(result)
        self.assertEqual('', result, "执行失败" + step_txt)

        step_txt = '----step3:比对执行结果; expect:执行结果正确----'
        shell = f'cd {self.target_path};' \
                f'diff {self.expect_name} {self.result_name}'
        result = self.pri_dbuser.sh(shell).result()
        self.log.info(result)
        self.assertEqual('', result, "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step_txt = '----清理数据; expect:清理成功----'
        self.log.info(step_txt)
        drop_sql = f"drop extension dblink cascade;"
        result1 = self.pri_sh.execut_db_sql(drop_sql, dbname=self.db_name)
        self.log.info(result1)
        drop_sql = f"drop database if exists {self.db_name};"
        result2 = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(result2)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
        self.assertIn(self.constant.drop_extension_success, result1,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, result2,
                      "执行失败" + step_txt)
